from sqlalchemy import select, update, delete
from sqlalchemy.orm import joinedload
from sqlalchemy.ext.asyncio import AsyncSession
from datetime import date

from module_kylin.entity.do.future_settlement_do import KylinFutureSettlement
from module_kylin.entity.vo.future_settlement_vo import (
    FutureSettlementModel,
    FutureSettlementPageQueryModel,
)
from utils.page_util import PageUtil


class FutureSettlementDao:
    """
    期货结算单数据库操作层
    """

    @classmethod
    async def add_settlement(cls, db: AsyncSession, settlement: FutureSettlementModel):
        """
        新增期货结算单

        :param db: orm对象
        :param settlement: 结算单信息
        :return: 操作结果
        """
        # 创建结算单对象
        settlement_do = KylinFutureSettlement(
            account_code=settlement.account_code,
            settlement_date=settlement.settlement_date,
            settlement_type=settlement.settlement_type or 'D',
            profit_calc_type=settlement.profit_calc_type or 'day',
            file_name=settlement.file_name,
            settlement_status=settlement.settlement_status or '0',
        )
        db.add(settlement_do)

    @classmethod
    async def get_settlement_by_id(cls, db: AsyncSession, settlement_id: int):
        """
        根据ID获取期货结算单信息

        :param db: orm对象
        :param settlement_id: 结算单id
        :return: 期货结算单信息对象
        """
        settlement_info = (
            (await db.execute(select(KylinFutureSettlement).where(KylinFutureSettlement.id == settlement_id)))
            .scalars()
            .first()
        )

        return settlement_info

    @classmethod
    async def get_settlement_by_date_and_account(
        cls, db: AsyncSession, settlement_date: date, account_code: str, profit_calc_type: str = None, settlement_type: str = None
    ):
        """
        根据结算日期和账户代码获取结算单信息

        :param db: orm对象
        :param settlement_date: 结算日期
        :param account_code: 账户代码
        :param profit_calc_type: 盈亏计算方式
        :param settlement_type: 结算单类型
        :return: 期货结算单信息对象
        """
        # 简化查询，移除joinedload('*')以避免异步上下文中的关联加载问题
        query = select(KylinFutureSettlement)

        # 基础查询条件
        query = query.where(KylinFutureSettlement.settlement_date == settlement_date)
        query = query.where(KylinFutureSettlement.account_code == account_code)

        # 如果提供了profit_calc_type，加入profit_calc_type的过滤条件
        if profit_calc_type:
            query = query.where(KylinFutureSettlement.profit_calc_type == profit_calc_type)
        
        # 如果提供了settlement_type，加入settlement_type的过滤条件
        if settlement_type:
            query = query.where(KylinFutureSettlement.settlement_type == settlement_type)

        # 执行查询并立即获取结果
        result = await db.execute(query)
        settlement_info = result.scalars().first()

        # 返回查询结果
        return settlement_info

    @classmethod
    async def get_settlement_by_date_and_account_exclude_id(
        cls, db: AsyncSession, settlement_date: date, account_code: str, exclude_id: int, 
        settlement_type: str = None, profit_calc_type: str = None
    ):
        """
        根据结算日期和账户代码获取结算单信息（排除指定ID）

        :param db: orm对象
        :param settlement_date: 结算日期
        :param account_code: 账户代码
        :param exclude_id: 排除的结算单ID
        :param settlement_type: 结算单类型
        :param profit_calc_type: 盈亏计算方式
        :return: 期货结算单信息对象
        """
        # 确保在异步上下文中正确执行查询
        query = select(KylinFutureSettlement).options(joinedload('*'))  # 加载所有关联数据，避免延迟加载
        query = query.where(KylinFutureSettlement.settlement_date == settlement_date)
        query = query.where(KylinFutureSettlement.account_code == account_code)
        query = query.where(KylinFutureSettlement.id != exclude_id)
        
        # 如果提供了settlement_type，加入settlement_type的过滤条件
        if settlement_type:
            query = query.where(KylinFutureSettlement.settlement_type == settlement_type)
        
        # 如果提供了profit_calc_type，加入profit_calc_type的过滤条件
        if profit_calc_type:
            query = query.where(KylinFutureSettlement.profit_calc_type == profit_calc_type)
        
        result = await db.execute(query)

        # 立即获取结果
        settlement_info = result.scalars().first()

        # 如果找到记录，确保所有必要的属性都被访问和缓存
        if settlement_info:
            # 预加载所有必要的属性，防止后续访问时触发额外查询
            _ = settlement_info.id
            _ = settlement_info.settlement_type
            _ = settlement_info.account_code
            _ = settlement_info.settlement_date
            _ = settlement_info.file_name
            _ = settlement_info.settlement_status

        return settlement_info

    @classmethod
    async def get_settlement_list(
        cls, db: AsyncSession, query_model: FutureSettlementPageQueryModel, is_page: bool = False
    ):
        """
        获取期货结算单列表

        :param db: orm对象
        :param query_model: 查询参数对象
        :param is_page: 是否开启分页
        :return: 期货结算单列表
        """
        query = select(KylinFutureSettlement)

        if query_model.account_code:
            query = query.where(KylinFutureSettlement.account_code == query_model.account_code)

        # 日期段查询：开始日期
        if query_model.begin_date:
            query = query.where(KylinFutureSettlement.settlement_date >= query_model.begin_date)

        # 日期段查询：结束日期
        if query_model.end_date:
            query = query.where(KylinFutureSettlement.settlement_date <= query_model.end_date)
            
        # 结算单类型查询
        if hasattr(query_model, 'settlement_type') and query_model.settlement_type:
            query = query.where(KylinFutureSettlement.settlement_type == query_model.settlement_type)
            
        # 盈亏计算方式查询
        if hasattr(query_model, 'profit_calc_type') and query_model.profit_calc_type:
            query = query.where(KylinFutureSettlement.profit_calc_type == query_model.profit_calc_type)
            
        # 结算单状态查询
        if hasattr(query_model, 'settlement_status') and query_model.settlement_status:
            query = query.where(KylinFutureSettlement.settlement_status == query_model.settlement_status)

        # 按结算日期降序排序
        query = query.order_by(KylinFutureSettlement.settlement_date.desc())

        # 构建分页查询
        settlement_page = await PageUtil.paginate(db, query, query_model.page_num, query_model.page_size, is_page)

        return settlement_page

    @classmethod
    async def update_settlement(cls, db: AsyncSession, settlement_id: int, settlement: FutureSettlementModel):
        """
        更新期货结算单

        :param db: orm对象
        :param settlement_id: 结算单id
        :param settlement: 期货结算单对象
        :return: 操作结果
        """
        update_data = {}
        if settlement.account_code is not None:
            update_data[KylinFutureSettlement.account_code] = settlement.account_code
        if settlement.settlement_date is not None:
            update_data[KylinFutureSettlement.settlement_date] = settlement.settlement_date
        if settlement.settlement_type is not None:
            update_data[KylinFutureSettlement.settlement_type] = settlement.settlement_type
        if settlement.profit_calc_type is not None:
            update_data[KylinFutureSettlement.profit_calc_type] = settlement.profit_calc_type
        if settlement.file_name is not None:
            update_data[KylinFutureSettlement.file_name] = settlement.file_name
        if settlement.settlement_status is not None:
            update_data[KylinFutureSettlement.settlement_status] = settlement.settlement_status

        await db.execute(
            update(KylinFutureSettlement).where(KylinFutureSettlement.id == settlement_id).values(update_data)
        )

    @classmethod
    async def delete_settlement(cls, db: AsyncSession, settlement_id: int):
        """
        删除期货结算单

        :param db: orm对象
        :param settlement_id: 结算单id
        :return: 操作结果
        """
        await db.execute(delete(KylinFutureSettlement).where(KylinFutureSettlement.id == settlement_id))

    @classmethod
    async def batch_delete_settlement(cls, db: AsyncSession, settlement_ids: list[int]):
        """
        批量删除期货结算单

        :param db: orm对象
        :param settlement_ids: 结算单id列表
        :return: 操作结果
        """
        await db.execute(delete(KylinFutureSettlement).where(KylinFutureSettlement.id.in_(settlement_ids)))
